import json

from pymysql import connect
from Order import Order

conc = connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    autocommit=True
)

cursor = conc.cursor()
conc.select_db("test")
try:
    cursor.execute("create table orders("
                   "id bigint primary key auto_increment comment 'id主键',"
                   "order_date DATE comment '日期',"
                   "order_id varchar(32) comment '订单编号',"
                   "order_sales int(4) comment '销售额',"
                   "order_province varchar(16) comment '销售省份'"
                   ") comment '销售额表'")
except Exception as e:
    print(e)
finally:
    cursor.close()
    conc.close()
data_list = []
order_list = []
sql = "insert into orders (order_date,order_id,order_sales,order_province) values (%s,%s,%s,%s)"
with open("2011年2月销售数据JSON.txt", "r", encoding="utf-8") as f:
    data = f.readlines()
    for e in data:
        new_data = json.loads(e)
        data_list.append(new_data)
for data in data_list:
    order = Order(data["date"], data["order_id"], data["money"], data["province"])
    order_list.append(order)
for order in order_list:
    values = [order.date, order.order_id, order.sales, order.province]
    cursor.execute(sql, values)
cursor.close()
conc.close()
